#CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
#GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
#GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
#GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

import re
from mapte import Mapte
from mapte.exceptions import UserNotFound
from multdb import new_session
import crypto

userlist = []
for k in Mapte.mysql_login_info().items():
  userlist.append(k[0])
print "Will add/overwrite the following users to the database on "+Mapte.get_current_host()+":\n"+str(userlist)
print "\nYou may need to enter the mysql root password twice."

session = new_session(Mapte.login('root'), False)

for user in userlist:
  #attempt to retrieve the password through the mapte module
  try:
    pw = Mapte.get_password(user)
  except UserNotFound:
    continue
  #produce a hash for this password with mysql's password command
  pw_mysql_hash_temp = session.execute("SELECT PASSWORD('"+pw+"')").fetchall()
  pw_mysql_hash = re.search(".*\'(.*)\'.*", str(pw_mysql_hash_temp)).group(1) #strip extra characters
  #create the given user and grant select/execute privileges for all databases
  # if session.execute("SELECT User from mysql.user where User='"+user+"' and Host='%'").fetchall():
  try:
    session.execute("DROP USER '"+user+"'@'%'")
  except:
    pass
  try:
    session.execute("DROP USER '"+user+"'@'localhost'")
  except:
    pass
  session.execute("CREATE USER '%s'@'%%' IDENTIFIED BY PASSWORD '%s'" % (user, pw_mysql_hash))
  session.execute("GRANT SELECT, EXECUTE ON *.* TO '%s'@'%%'" % user)
  session.execute("CREATE USER '%s'@'localhost' IDENTIFIED BY PASSWORD '%s'" % (user, pw_mysql_hash))
  session.execute("GRANT SELECT, EXECUTE ON *.* TO '%s'@'localhost'" % user)

if "root" in userlist:
  session.execute("GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION")
  session.execute("GRANT ALL ON *.* TO 'root'@'localhost' WITH GRANT OPTION")

if "loading" in userlist:
  #get list of rpt databases
  from mapte.utilities import get_database_list
  rpt_database_list = []
  for db in get_database_list(session):
    if str(db).find("rpt_database") > -1:
      rpt_database_list.append(db)

  #grant privileges to loading user
  for db_temp in rpt_database_list:
    db = re.search(".*\'(.*)\'.*", str(db_temp)).group(1) #strip extra characters
    session.execute("GRANT INSERT,UPDATE,DELETE ON `"+str(db)+"`.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON pts_truth.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON manual_data.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON reduce.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON track_matching.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON finished.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON metadata.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON trajectory_db.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON waves.* TO 'loading'")
  session.execute("GRANT INSERT,UPDATE,DELETE ON warn.* TO 'loading'")
  session.execute("GRANT ALTER ON raw_rpt_database.* TO 'loading'")

if "trackmatching" in userlist:
  #grant privileges to trackmatching user
  session.execute("GRANT ALL ON track_matching.* TO 'trackmatching'")

if "res" in userlist:
  #grant privileges to res user
  session.execute("GRANT ALL ON res_database.* TO 'res'")

if "finished" in userlist:
  #grant privileges to finished user
  session.execute("GRANT ALL ON finished.* TO 'finished'")

if "radar" in userlist:
  #grant privileges to radar user
  session.execute("GRANT ALL ON radar_team.* TO 'radar'")

session.commit()
session.close()

print "Done"


